package zy.dao.base.stream.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.stream.StreamDAO;
import zy.entity.base.stream.T_Base_Stream;
@Repository
public class StreamDAOImpl extends BaseDaoImpl implements StreamDAO {

	@Override
	public Integer count(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_stream t");
		sql.append(" where 1 = 1");
		sql.append(" AND se_sp_code = :shop_code ");//上级店铺编号
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.se_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Stream> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object start = param.get("start");
		Object end = param.get("end");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select se_id,se_code,se_name,se_man,se_tel,se_addr,se_mobile,companyid,");
		sql.append(" IFNULL(se_init_debt,0) AS se_init_debt,IFNULL(se_payable,0) AS se_payable,IFNULL(se_payabled,0) AS se_payabled");
		sql.append(" from t_base_stream t");
		sql.append(" where 1 = 1");
		sql.append(" AND se_sp_code = :shop_code ");//上级店铺编号
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.se_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by se_id desc");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Base_Stream> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Stream.class));
		return list;
	}
	
	@Override
	public T_Base_Stream check(T_Base_Stream stream) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT se_id,se_code,companyid,se_name");
		sql.append(" FROM t_base_stream t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND se_sp_code = :se_sp_code ");//上级店铺编号
		sql.append(" AND se_name = :se_name");
		sql.append(" AND t.companyid = :companyid");
		if(stream.getSe_id() != null && stream.getSe_id()>0 ){
			sql.append(" AND se_id <> :se_id");
		}
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),new BeanPropertySqlParameterSource(stream),
					new BeanPropertyRowMapper<>(T_Base_Stream.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Base_Stream model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_three_code(max(se_code+0)) from t_base_stream ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setSe_code(code);
		sql.setLength(0);
		
		sql.append("INSERT INTO t_base_stream");
		sql.append(" (se_code,se_name,se_spell,se_man,se_tel,se_addr,se_mobile,se_init_debt,se_payable,se_payabled,se_sp_code,companyid)");
		sql.append(" VALUES(:se_code,:se_name,:se_spell,:se_man,:se_tel,:se_addr,:se_mobile,:se_init_debt,:se_payable,:se_payabled,:se_sp_code,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model),holder);
		int id = holder.getKey().intValue();
		model.setSe_id(id);
	}

	@Override
	public void update(T_Base_Stream model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_base_stream");
		sql.append(" set se_name=:se_name");
		sql.append(" ,se_spell=:se_spell");
		sql.append(" ,se_man=:se_man");
		sql.append(" ,se_tel=:se_tel");
		sql.append(" ,se_addr=:se_addr");
		sql.append(" ,se_mobile=:se_mobile");
		sql.append(" where se_id=:se_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public T_Base_Stream queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select se_id,se_code,se_name,se_man,se_tel,se_addr,se_mobile,companyid");
		sql.append(" from t_base_stream ");
		sql.append(" where se_id=:se_id");
		try{
			T_Base_Stream data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("se_id", id),new BeanPropertyRowMapper<>(T_Base_Stream.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void del(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_stream");
		sql.append(" WHERE se_id=:se_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("se_id", id));
	}

	@Override
	public T_Base_Stream loadStream(String se_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT se_id,se_code,se_name,");
		sql.append(" IFNULL(se_payable,0) AS se_payable,IFNULL(se_payabled,0) AS se_payabled");
		sql.append(" FROM t_base_stream t");
		sql.append(" WHERE se_code = :se_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("se_code", se_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Stream.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updateSettle(T_Base_Stream stream) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_stream ");
		sql.append(" SET se_payable=:se_payable ");
		sql.append(" ,se_payabled=:se_payabled");
		sql.append(" WHERE se_id=:se_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(stream));
	}
	
	
}
